package sysUtil

import initConfig.SysInitData
import sysAuth.SysRoles
import sysAuth.SysUsers

/**
 * Created by Administrator on 2018-05-21.
 */
class SysAuthSQL {



    /*
     * 根据角色列表：根据用户ID，角色类型，获取权限ID
     * @param roles
     * @param roleType
     * @return
     */
    public static  String authDataDBSql(String userNo,String roleType ){
        String sql = """
               SELECT T1.DATA_ID
                  FROM SYS_USER_ROLES T
                 INNER JOIN SYS_ROLE_RELA T1
                    ON T.ROLE_ID = T1.ROLE_ID
                   AND T1.ROLE_TYPE = '${roleType}'
                 WHERE T.USER_ID IN (SELECT ID FROM SYS_USERS T WHERE T.USER_NO = '${userNo}')
                """
        return sql.toString()
    }

    /**
     * 查询用户列表
     * @param userNo
     * @param authSql 数据权限
     * @param searchStr 查询字符串
     * @param deptId 部门ID
     * @return
     */
    public static String getUserListSQL(  userNo, userGrade, String searchStr,  deptId){
        def authSql = authDataDBSql(userNo, SysInitData.DATA_ROLE)
        def sql = """
        SELECT T.ID,
               T.USER_NAME,
               T.USER_NO,
               T.PASS_WORD,
               T.DEPT_ID,
               T.BIRTH,
               TA.NAME DEPT_NAME,
               T.GENDER,
               TB.label GENDER_NAME,
               T.EMAIL,
               T.PHONE,
               T.QQ,
               T.WEI_XIN,
               T.IS_ACTIVE,
               T.ORG_ID,
               TC.NAME ORG_NAME,
               TB3.label EDUCATION,
               TB2.label POSITION,
               t.SALARY,
               t.ADDRESS
          FROM SYS_USERS T
          LEFT JOIN SYS_DEPARTMENTS TA
            ON T.DEPT_ID = TA.ID
          left join sys_params tb on t.gender = tb.value and tb.params_type = 'USER_SEX'
          left join sys_params tb2 on t.POSITION = tb2.value and tb2.params_type = 'POSITION'
          left join sys_params tb3 on t.EDUCATION = tb3.value and tb3.params_type = 'EDUCATION'
          LEFT JOIN sys_organization TC ON T.ORG_ID = TC.ID
         WHERE 1 = 1
            ${!SysInitData.USER_GRADE_ADMIN_DATA.contains(userGrade)? " AND ( T.CREATOR = '${userNo}' OR T.USER_NO = '${userNo}' OR T.org_id IN (${authSql}) )":"" }
            ${searchStr?" AND (T.USER_NAME LIKE '%${searchStr}%' OR T.USER_NO LIKE '%${searchStr.toUpperCase()}%')":""}
            ${deptId?" AND TA.ID in(${deptId})":""}
         ORDER BY T.LAST_UPDATED DESC
    """

        return sql.toString()
    }

    /**
     * 获取部门，根据用户拥有的数据权限中，用户所建的部门展示
     * @param userNo
     * @param userGrade
     * @return
     */
    static String getDeptSql(userNo,userGrade){
        def sql = """
        SELECT ID,
               CREATOR,
               DATE_CREATED,
               DEPT_NO,
               LAST_UPDATED,
               MARKS,
               NAME,
               ORDER_NUM,
               PARENT_ID,
               STATE,
               UPDATER
          FROM SYS_DEPARTMENTS dept
        where dept.creator in (  ${getUserNoByAuth(userNo)}  )
        ORDER BY PARENT_ID,ORDER_NUM
        """

        return  sql.toString()
    }

    /**
     * 获取菜单配置的操作按钮
     * @param funcId
     * @return
     */
    static getFuncOperate(funcId){
        def sql = ""
        if(funcId){
            sql = """
        SELECT distinct TA.ID  OPERATE_ID,
               TA.OPERATE_NO,
               TA.OPERATE_NAME,
               TA.STYLE,
               TA.ICON,
               TA.MARKS,
               TA.TYPE,
               TA.ORDER_NUM,
               ${funcId?:''}    FUNCTION_ID,
               TB.STATE,
               TB.ACTION
          FROM SYS_OPERATIONS TA
          LEFT JOIN SYS_FUN_OPT TB
            ON TA.ID = TB.OPERATE_ID
           ${funcId?"AND TB.FUNCTION_ID =${funcId}":""}
          order by ta.ORDER_NUM
        """
        }else {
            sql = """
        SELECT TA.ID  OPERATE_ID,
               TA.OPERATE_NO,
               TA.OPERATE_NAME,
               TA.STYLE,
               TA.ICON,
               TA.MARKS,
               TA.TYPE,
               TA.ORDER_NUM
          FROM SYS_OPERATIONS TA
          order by ta.last_updated desc
        """
        }
//        println sql
        return sql.toString()
    }

    /**
     * 根据用户权限获取菜单按钮
     * @param userNo
     * @param funcId
     * @param userGrade
     */
    static getFuncOptsByAuth(userNo,funcId,userGrade,roleId){
        def roleType = SysInitData.FUNC_ROLE;

        String sql = """
        SELECT T4.OPERATE_NO,
               T4.OPERATE_NAME,
               T4.ICON,
               T4.STYLE,
               T4.TYPE,
               T3.ACTION,
               T3.OPERATE_ID,
               T3.FUNCTION_ID,
               T3.ID
          FROM SYS_ROLE_RELA_OPERAT T1,
               SYS_ROLE_RELA        T2,
               SYS_FUN_OPT          T3,
               SYS_OPERATIONS       T4
         WHERE T1.RELA_ID = T2.ID
           AND T1.SYS_FUN_OPT_ID = T3.ID
           AND T2.DATA_ID = T3.FUNCTION_ID
           AND T3.OPERATE_ID = T4.ID
           AND T3.STATE = 1
            """
        if(SysInitData.USER_GRADE_ADMIN_FUNC.contains(userGrade)){
            sql = """
                SELECT T4.OPERATE_NO,
                       T4.OPERATE_NAME,
                       T4.ICON,
                       T4.STYLE,
                       T4.TYPE,
                       T3.ACTION,
                       T3.OPERATE_ID,
                       T3.FUNCTION_ID,
                       T3.ID
                  FROM SYS_FUN_OPT          T3,
                       SYS_OPERATIONS       T4
                 WHERE 1=1
                   AND T3.OPERATE_ID = T4.ID
                   AND T3.STATE = 1
                    """
        }

        def condition = ""
        if(!SysInitData.USER_GRADE_ADMIN_FUNC.contains(userGrade)){
            condition += """
                AND T2.ROLE_ID IN (
                   SELECT T1.ROLE_ID
                         FROM SYS_USER_ROLES T1,
                              SYS_USERS      T2
                        WHERE T1.USER_ID = T2.ID
                          AND T2.USER_NO = '${userNo}'
                    UNION SELECT ID FROM SYS_ROLES t WHERE t.CREATOR = '${userNo}'
             )
            """

            if(funcId){
                condition += """
            AND T2.DATA_ID = ${funcId}
            """
            }else{
                condition += " and 1=2"
            }
        }else{
            if(funcId){
                condition += """
                and   T3.FUNCTION_ID = ${funcId}
            """
            }
        }
        //println sql + condition + " order by T4.ORDER_NUM"
        return sql + condition + " order by T4.ORDER_NUM"
    }

    /**
     * 获取角色拥有的操作操作按钮
     * @param roleId
     * @return
     */
    static getFuncOptsByRole(roleId){
        String sql = """
        SELECT T4.OPERATE_NO,
               T4.OPERATE_NAME,
               T4.ICON,
               T4.STYLE,
               T4.TYPE,
               T3.ACTION,
               T3.OPERATE_ID,
               T3.FUNCTION_ID,
               T3.ID
          FROM SYS_ROLE_RELA_OPERAT T1,
               SYS_ROLE_RELA        T2,
               SYS_FUN_OPT          T3,
               SYS_OPERATIONS       T4
         WHERE T1.RELA_ID = T2.ID
           AND T1.SYS_FUN_OPT_ID = T3.ID
           AND T3.OPERATE_ID = T4.ID
           AND T2.DATA_ID = T3.FUNCTION_ID
           AND T3.STATE = 1
           and T2.ROLE_ID = ${roleId}
            """
        return sql
    }


    /**
     * 获取用户拥有的权限，并可查询authUser 的已经赋予的权限
     * @param userNo
     * @param roleType
     * @param userGrade
     * @param authUser
     * @return
     */
    static getRoleListSQL(userNo,roleType,userGrade,authUserId){
        //println SysInitData.USER_GRADE_ADMIN_DATA.contains(userGrade)
        def sql = """
        SELECT distinct T.ID,
               T.ROLE_NAME,
               T.ROLE_NO,
               T.ROLE_TYPE,
               T.CREATOR,
               T.MARKS,
               T1.ROLE_ID,
               T.LAST_UPDATED
          FROM SYS_ROLES T
        left join SYS_USER_ROLES T1 on  T.ID = T1.ROLE_ID
        ${!authUserId?"":
                " and t1.USER_ID =  '${authUserId}'"}
         WHERE 1 = 1
           AND T.ROLE_TYPE = '${roleType?:SysInitData.FUNC_ROLE}'
           ${SysInitData.USER_GRADE_ADMIN_DATA.contains(userGrade)?"":" AND T.CREATOR = '${userNo}'"}
         ORDER BY T.LAST_UPDATED DESC
        """
        return sql.toString()
    }

    /**
     * 获取xdDataSource清单
     * @param userNo
     */
    static getXdDataSourceSQL(userNo,userGrade,searchStr,state){
        def sql = """
    SELECT ID,
           CREATOR,
           DRIVER_CLASS_NAME,
           PASSWORD,
           SOURCE_NAME,
           STATE,
           URL,
           USERNAME
      FROM XD_DATA_SOURCE A
     WHERE 1 = 1
       ${
            if(SysInitData.USER_GRADE_ADMIN_FUNC.contains(userGrade) || SysInitData.USER_GRADE_ADMIN_DATA.contains(userGrade) ){
                ""
            }else if(userNo){
                " AND A.CREATOR IN (${getUserNoByAuth(userNo)})"
            }else{
                ""
            }
        }
       ${
            if(searchStr){
                "and a.source_Name like '%${searchStr}%'"
            }else{
                ""
            }
        }
       ${
            if(state){
                " and a.STATE= '${state}' "
            }else{
                ""
            }
        }
     ORDER BY A.LAST_UPDATED DESC
    """
        return sql.toString()
    }

    /**
     * 获取登陆用户拥有数据权限下，所有账号
     * @param userNo
     */
    static getUserNoByAuth(userNo){
        def sql ="""
        SELECT T4.USER_NO
          FROM SYS_USERS T4
         WHERE 1=1
            AND (T4.CREATOR = '${userNo}'
            OR T4.ORG_ID IN ( ${authDataDBSql(userNo,SysInitData.DATA_ROLE)}))
         """
        return sql.toString()
    }

    /**
     *
     * @param userNo
     * @param state 状态：1 未读，0 失效:已读
     * @param type ：类型 1 private 0 public
     * @param userGrade
     * @param delFlag ：删除标识：0 否 ，1 是
     * @return
     */
    static getNoticeSql(userNo,state,type ,userGrade,delFlag){
        def sql = """
            SELECT T.ID,
                   T.CONTENT,
                   T.CREATOR,
                   SU1.USER_NAME CREATOR_NAME,
                   T.DATE_CREATED,
                   T.LAST_UPDATED,
                   T.REMAIN_DAY,
                   T.PARENT_ID,
                   T.STATE,
                   T.TITLE,
                   T.TYPE,
                   T.UPDATER,
                   T.TO_USER,
                   su.USER_NAME TO_USER_NAME,
                   (SELECT COUNT(1)
                      FROM SYS_NOTICE SYSN
                     WHERE SYSN.STATE = '1'
                       AND SYSN.DEL_FLAG = '0'
                       AND SYSN.TO_USER = '${userNo}'
                       AND (SYSN.ID = T.ID OR SYSN.PARENT_ID = T.ID)) NOTICE_COUNT
              FROM SYS_NOTICE T
              LEFT JOIN SYS_USERS SU
                ON T.TO_USER = SU.USER_NO
              LEFT JOIN SYS_USERS SU1
                ON T.CREATOR = SU1.USER_NO
             WHERE T.DEL_FLAG = '${delFlag?:"0"}'
               AND T.PARENT_ID IS NULL
               AND T.TYPE = '${type?:"-1"}'
              ${state?" AND T.STATE = '${state}' ":""}

             ${
            if(type=="1" ){//如果是私信,并且统计清单list
               "and  (t.TO_USER = '${userNo}' OR  t.CREATOR = '${userNo}' )"
            }else {
                if(!SysInitData.USER_GRADE_ADMIN_DATA.contains(userGrade)){
                   """  AND EXISTS(SELECT 1 FROM Sys_Notice_Rela SNR ,sys_users snru
                        WHERE SNR.notice_Id = T.ID and snru.org_id = snr.org_id and snru.user_no='${userNo}')
                """
                }else{
                    ""
                }
            }
        }
             ORDER BY T.DATE_CREATED DESC
            """

        return  sql.toString()
    }

    /**
     * 获取首页通知数量
     * @param userNo
     * @param type
     */
    static getIndexNoticeCountByType(String userNo,String type,userGrade){
        def sql = """
        SELECT  1
        FROM SYS_NOTICE TA
        WHERE TA.del_flag = '0'
        AND TA.STATE = '1'
        ${type?" AND ta.type = '${type}'":""}
        ${
            if(type=="1" ){//如果是私信,并且统计清单list
                "and  ta.TO_USER = '${userNo}' "
            }else {
                if(!SysInitData.USER_GRADE_ADMIN_DATA.contains(userGrade)){
                    """  AND EXISTS(SELECT 1 FROM Sys_Notice_Rela SNR ,sys_users snru
                        WHERE SNR.notice_Id = TA.ID and snru.org_id = snr.org_id
                          and snru.user_no='${userNo}')
                 """
                }else{
                    ""
                }
            }
        }

    """
        return  sql
    }

    /**
     * 通过ID，获取所有notice
     * @param id
     * @return
     */
    static getAllNoticeByIdSql(id,toUser){
        def sql = """
        SELECT SYS_NOTICE.ID,
               SYS_NOTICE.CONTENT,
               SYS_NOTICE.CREATOR,
               SU.USER_NAME,
               SYS_NOTICE.DATE_CREATED,
               SYS_NOTICE.DEL_FLAG,
               SYS_NOTICE.LAST_UPDATED,
               SYS_NOTICE.PARENT_ID,
               SYS_NOTICE.REMAIN_DAY,
               SYS_NOTICE.STATE,
               SYS_NOTICE.TITLE,
               SYS_NOTICE.TO_USER,
               SUT.USER_NAME TO_USER_NAME,
               SYS_NOTICE.TYPE,
               SYS_NOTICE.UPDATER
          FROM SYS_NOTICE SYS_NOTICE
          LEFT JOIN SYS_USERS SU ON SYS_NOTICE.CREATOR = SU.USER_NO
          LEFT JOIN SYS_USERS SUT ON SYS_NOTICE.TO_USER = SUT.USER_NO
         WHERE SYS_NOTICE.DEL_FLAG = '0'
           AND (SYS_NOTICE.ID = ${id} OR SYS_NOTICE.PARENT_ID = ${id})
           ${toUser?"AND SYS_NOTICE.TO_USER = '${toUser}'":""}
           order by SYS_NOTICE.date_created
        """
        return sql
    }

    /**
     * 获取参数列表
     * @param paramsType
     * @param keyword
     * @return
     */
    static getParams(paramsType,keyword,state,id){
        def sql = """
    SELECT T.ID,
           T.PARAMS_TYPE,
           T.LABEL,
           T.VALUE,
           T.ORDER_NUM,
           T.PARAMS_DESC,
           T.DATE_CREATED,
           T.LAST_UPDATED,
           T.STATE,
           T.CREATOR,
           T.UPDATER
      FROM SYS_PARAMS T
    WHERE 1=1
    ${id?" AND T.ID = ${id}":""}
    ${state?" and t.STATE = ${state} ":""}
    ${paramsType?" AND T.PARAMS_TYPE = '${paramsType}'":""}
    ${keyword ?" and t.PARAMS_TYPE like '%${keyword}%' or t.LABEL like '%${keyword}%'":""}

    ORDER BY PARAMS_TYPE,ORDER_NUM
    """
        return sql.toString()
    }


    /**
     * 获取widget
     */
    static getWidgetSQL(params){
        def panelId = params?.panelId
        def searchStr = params?.searchStr


        def panelRelSql = ""
        if(panelId){
            panelRelSql += """
            left join xd_widget_panel_param tc on tc.param_id = ta.id and tc.panel_id = ${panelId}
                                                  and tc.class_flag = tb.CLASS_FLAG
        """
        }


        def sql = """
        SELECT TA.ID,TA.NAME,TA.LABEL,TA.XD_WIDGET_ID,TA.LAST_UPDATED, TB.NAME XD_WIDGET,tb.CLASS_FLAG,TA.CREATOR
            ${panelId?",TC.PANEL_ID":""}
          FROM (SELECT T.ID,
                       T.NAME,T.LABEL,
                       T.XD_WIDGET_ID,
                       T.LAST_UPDATED,
                       t.CREATOR
                  FROM XD_WIDGET_INPUT T
                UNION
                SELECT T.ID,
                       T.NAME,T.LABEL,
                       T.XD_WIDGET_ID,
                       T.LAST_UPDATED,
                       t.CREATOR
                  FROM XD_WIDGET_DATE T
                UNION
                SELECT T.ID,
                       T.NAME,T.LABEL,
                       T.XD_WIDGET_ID,
                       T.LAST_UPDATED,
                       t.CREATOR
                  FROM XD_WIDGET_SELECT T) TA
         inner join XD_WIDGET TB on TA.XD_WIDGET_ID = TB.ID
         ${panelRelSql}
         WHERE 1=1
         """
        if(searchStr){
            sql += """
            AND ( TA.NAME LIKE '%${searchStr}%'
                 OR TA.LABEL LIKE '%${searchStr}%'
                )
        """
        }

        sql += """ order by  ${panelId?" TC.order_Num ":" ta.LAST_UPDATED desc"}    """

       // println sql

        return sql.toString()
    }


    /**
     * 获取widget-panel-绑定的-param
     */
    static getWidgetPanelParamSQL(params){
        def panelId = params?.panelId

        def sql = """
        SELECT TA.ID PARAM_ID,TA.NAME,TA.LABEL,TB.NAME XD_WIDGET,tb.CLASS_FLAG,TC.PANEL_ID,TC.ID
          FROM (SELECT T.ID,
                       T.NAME,T.LABEL,
                       T.XD_WIDGET_ID,
                       T.LAST_UPDATED,
                       t.CREATOR
                  FROM XD_WIDGET_INPUT T
                UNION
                SELECT T.ID,
                       T.NAME,T.LABEL,
                       T.XD_WIDGET_ID,
                       T.LAST_UPDATED,
                       t.CREATOR
                  FROM XD_WIDGET_DATE T
                UNION
                SELECT T.ID,
                       T.NAME,T.LABEL,
                       T.XD_WIDGET_ID,
                       T.LAST_UPDATED,
                       t.CREATOR
                  FROM XD_WIDGET_SELECT T) TA
         inner join XD_WIDGET TB on TA.XD_WIDGET_ID = TB.ID
         inner join xd_widget_panel_param tc on tc.param_id = ta.id and tc.panel_id = ${panelId}
                                                  and tc.class_flag = tb.CLASS_FLAG
         WHERE 1=1
         """
        sql += """ order by  TC.order_Num """
        return sql.toString()
    }

    /**
     * 获取widget
     */
     static getWidgetPanelSQL(searchStr){
         def sql = """
        SELECT TA.ID,TA.NAME,TA.LABEL,TA.XD_WIDGET_ID,TA.LAST_UPDATED, TB.NAME XD_WIDGET,tb.CLASS_FLAG,TA.CREATOR
          FROM (SELECT T.ID,
                       T.NAME,T.LABEL,
                       T.XD_WIDGET_ID,
                       T.LAST_UPDATED,
                       t.CREATOR
                  FROM xd_widget_panel T
                ) TA,
               XD_WIDGET TB
         WHERE TA.XD_WIDGET_ID = TB.ID
         """
         if(searchStr){

             if(String.valueOf(searchStr).indexOf("'") != -1){
                 sql+=""" and 1=2 """
             }else{
                 sql += """
                AND ( TA.NAME LIKE '%${searchStr}%'
                 OR TA.LABEL LIKE '%${searchStr}%') """
             }
         }

         sql +=" order by ta.LAST_UPDATED desc"

         //println sql
         return sql.toString()
     }


    /**
     * 获取widget
     */
    static getWidgetReportSQL(searchStr){
        def sql = """
            SELECT ID,LAYOUT,NAME ,TEMP_HEADER,TEMP_BODY,PAGE_HEADER,PAGE_BODY FROM  xd_widget_report ta where 1=1
         """
        if(searchStr && searchStr != "null") {
            if (String.valueOf(searchStr).indexOf("'") != -1) {
                sql += """ and 1=2 """
            } else {
                sql += """ AND TA.NAME LIKE '%${searchStr}%' """
            }
        }
        return sql.toString()
    }

    /**
     * 获取还未配置rpt-panel
     * @param searchStr
     * @return
     */
    static getWidgetReportPanelSQL(rptId,panelName){
        def sql =  """
         SELECT ta.ID,ta.NAME, tta.name WIDGET_NAME FROM xd_widget_panel ta
          INNER JOIN xd_widget tta ON ta.xd_widget_id = tta.id
         WHERE 1=1
         AND NOT EXISTS(SELECT 1  FROM  xd_widget_report_panel tb
         WHERE ta.id = tb.panel_id AND tb.rpt_id = ${rptId})
            """
        if(panelName && panelName != "null"){
            sql += """
            AND ( TA.NAME LIKE '%${panelName}%' or ta.Id like '${panelName}%')
        """
        }
        return sql.toString()
    }

    /**
     * 获取widget-panel-绑定的-param
     */
    static getWidgetRptParamSQL(rptId ,inflag){

        def sql = """
        SELECT DISTINCT TA.NAME,
               TA.LABEL,
               TB.NAME  XD_WIDGET,
               tc.param_id ID,
               tc.CLASS_FLAG
          FROM (SELECT T.ID,
                       T.NAME,
                       T.LABEL,
                       T.XD_WIDGET_ID
                  FROM XD_WIDGET_INPUT T
                UNION
                SELECT T.ID,
                       T.NAME,
                       T.LABEL,
                       T.XD_WIDGET_ID
                  FROM XD_WIDGET_DATE T
                UNION
                SELECT T.ID,
                       T.NAME,
                       T.LABEL,
                       T.XD_WIDGET_ID
                  FROM XD_WIDGET_SELECT T) TA
         INNER JOIN XD_WIDGET TB
            ON TA.XD_WIDGET_ID = TB.ID
         INNER JOIN XD_WIDGET_PANEL_PARAM TC
            ON TC.PARAM_ID = TA.ID
           AND TC.CLASS_FLAG = TB.CLASS_FLAG
           AND TC.PANEL_ID IN (SELECT PANEL_ID FROM xd_widget_report_panel WHERE RPT_ID = ${rptId})
           AND ${inflag?:""} EXISTS(SELECT 1 FROM XD_WIDGET_REPORT_PARAM TD
                                  WHERE 1=1
                                   AND TD.CLASS_FLAG  = TC.CLASS_FLAG
                                   AND TD.param_id = TC.param_id
                                   AND TD.RPT_ID = ${rptId} )
         WHERE 1 = 1
         """
        //println sql
        return sql.toString()
    }

    /**
     * 删除 rpt-panel-act
     * @param rptId
     */
    static deleteRptPanelActById(rptId){
        def sql = """delete from xd_widget_report_panel_act where  rpt_id = ${rptId}"""
        return  sql.toString()
    }


    /**
     * 删除 rpt-panel-act
     * @param rptId
     */
    static deleteRptPanelById(rptId){
        def sql = """delete from xd_widget_report_panel where  rpt_id = ${rptId}"""
        return  sql.toString()
    }

    /**
     * 插入rpt-panel-act
     * @param rptId
     */
    static insertRptPanelActById(rptId){
        def sql = """insert INTO xd_widget_report_panel_act
            (id,order_num,panel_id,rpt_id)
            select id,order_num,panel_id,rpt_id from
            xd_widget_report_panel t where 1=1
            and t.rpt_id = ${rptId}
            """
        return  sql.toString()
    }


    /**
     * 删除 rpt-panel-act
     * @param rptId
     */
    static deleteRptParamActById(rptId){
        def sql = """delete from xd_widget_report_param_act where  rpt_id = ${rptId}"""
        return  sql.toString()
    }

    /**
     * 删除 rpt-panel-act
     * @param rptId
     */
    static deleteRptParamById(rptId){
        def sql = """delete from xd_widget_report_param where  rpt_id = ${rptId}"""
        return  sql.toString()
    }
    /**
     * 插入rpt-panel-act
     * @param rptId
     */
    static insertRptParamActById(rptId){
        def sql = """  INSERT INTO xd_widget_report_param_act
            (id,  class_flag, order_num,param_id, rpt_id)
            select id,  class_flag, order_num,param_id, rpt_id
             from xd_widget_report_param t where 1=1
                and t.rpt_id = ${rptId}
            """
        return  sql.toString()
    }

    /**
     * 获取除自身外dataType=sql 的panel
     * @param selfId
     * @param keyWork
     */
    static getPanelExceptSelf(selfId,searchStr){
        def sql = """
      SELECT TA.ID,TA.NAME,TA.LABEL
          FROM (SELECT T.ID,
                       T.NAME,T.LABEL,
                       T.XD_WIDGET_ID,
                       T.LAST_UPDATED,
                       t.CREATOR
                  FROM xd_widget_panel T
                    WHERE T.DATA_TYPE='SQL'
                ) TA,
               XD_WIDGET TB
         WHERE TA.XD_WIDGET_ID = TB.ID
         AND TA.ID != ${selfId}
         """
        if(searchStr){

            if(String.valueOf(searchStr).indexOf("'") != -1){
                sql+=""" and 1=2 """
            }else{
                sql += """
                AND ( TA.NAME LIKE '%${searchStr}%'
                 OR TA.LABEL LIKE '%${searchStr}%') """
            }
        }

        sql +=" order by ta.LAST_UPDATED desc"
    }

}
